<?php
########################################################
# DMS Donations Management System v1.0                 #
# created by:  Isaac Sabas                             #
#			   Nikko Reyes							   #
#			   Miko Tiamwatt						   #
#                                                      #
# De La Salle Philippines                              #
# All Rights Reserved DLSP Copyright 2009              #
########################################################

# INFORMATION:
# ClientHandle.php 
# 	- this is the class that handles the connection of the client window to the database
#	- all MODIFY, SELECT, and DELETE functions are housed in this class
# 	
# FUNCTION INFORMATION:
#	1. CONSTRUCTOR:
#		- sets up the database connection
#		- the first constructor establishes basic database connection
#		- the second constructor gets the basic information of the client, provides the interface of client data handling, data is accessed locally on the class 
#	2. LOAD INFORMATION DATA:
#		a. getClientProfile (clientId) - returns all the data fields of a particular client
#		b. getPhoneNumbers (clientId) - returns all the phone number and its details of a particular client
#		c. getMobileNumbers (clientId) - returns all the mobile phone number and its details of a particular client
#		d. getAddress (clientId) - returns all the addresses and its details of a particular client
#		e. getProfileItem (item, clientId) - returns a particular data item of a particular client
#	3. GETTERS:
#		a. getProfileValues () - returns the class profileArray attribute
#		b. getAddressValues () - returns the class addressArray attribute
#		c. getPhoneNumberValues () - returns the class phoneArray attribute
#		d. getMobileNumberValues () - returns the class mobileArray attribute
#		e. getProfileValue (item) - returns a specific value of the profileArray
#	4. STORE DATA:
#		a. addNewClient (valuesArray, agentId)  - adds a new client
# 		b. addFollowUp (valuesArray, clientId) - add the client to the followup list
#		c. addNewReferral (profileValuesArray, phoneValuesArray, accountId, clientId) - adds a new referral and associates the agent who handled the call, and client who referred the new client
#		d. addNewAddress (valuesArray, clientId) - adds a new address to a particular client
#		f. addNewPhoneNumber (valuesArray, clientId) - adds a new phone number to a particular client
#		g. addNewMobileNumber (valuesArray, clientId) - adds a new mobile number to a particular client
#		h. tagRefer (clientId, refferedId, accountId) - tags the new client to the client who referred him/her and the agent who handled the call
# 	5. MODIFY DATA:
#		a. setProfileValue (item, value, clientId) - modifies an item in the profileArray and updates the database
# 		b. setAddressValue (addressId, item, value, clientId) - modifies an item in the addressArray and updates the database
#		c. setPhoneNumberValue (phoneId, item, value, clientId) - modifies an item in the phoneArray and updates the database
# 		d. setMobileNumberValue (mobileId, item, value, clientId) - modifies an item in the mobileArray and updates the database
#	6. SYSTEM UTILITY FUNCTIONS:
#		a. isError (value) - determines if the system message is an error or not
#

class ClientHandle{# FOR CLASS MANAGEMENT #								  
# 	The following variables are set to uppercase. This means that they are    
#	class monitoring variables.									              

	# CLASS MESSAGE_HANDLE HOLDER #
	# handles the error and notification messages of the class to notify the user 
	# to capture all SQL and system error, notifications and logs 
		public $MESSAGE_HANDLE = NULL;

	# DB_HANDLE INSTANCE HOLDER #
	# database connection handler [ MYSQL ]
	# instance of the database handle is placed here for the system to use the database with ease 
		public $DB_HANDLE = NULL;
		
# CLASS CONSTANT VARIABLES #
# this is for class management purposes
	private $errorArray = array("DB_NOTCONNECTED" => "Not connected to database.",
							    "DB_QUERYERROR" => "Problem with query",
							    "DB_INVALIDFIELD" => "Invalid field being accessed."
							   );
							   
	# CLIENT TYPE:
	# 0 = disabled
	# 1 = not referred and new
	# 2 = referred
	# 3 = multiple referrals
	
# CLIENT VARIABLES #
# the client variables represent the data fields of the database itself
	/*
	public $firstName;
	public $lastName;
	public $middleName;
	public $nickName; 
	public $gender;
	public $birthday;
	public $batch;
	public $courseCode;
	public $courseName;
	public $yearGraduated;
	public $school;
	public $company;
	public $position;
	public $email;
	public $deceased;
	public $dateOfDeath;
	public $type;
	public $alumni;
	public $commulativeDonationAmount;
	*/
	private $profileArray;		// an array
	private $addressArray;		// an array of address due to multiple home addresses
	private $phoneArray; 		// an array of phone numbers due to multiple phones subscribed
	private $mobileArray;		// an array of mobile phone numbers due to multiple mobile phones owned
	
###################################
# CLIENT HANDLE CLASS CONSTRUCTOR #
###################################
	public function ClientHandle () {
		# include the needed / required library
			require_once('DBHandle.php');
		# instantiate the DBHandle and assign it to the class attribute DB_HANDLE	
			$databaseConnection = new DBHandle();
			$this->DB_HANDLE = $databaseConnection->DB_HANDLE;
	}

	

##############################	
# LOAD SYSTEM DATA FUNCTIONS #	
##############################		
	public function getClient ($clientId) {		
		# 1. get the profile values from the database	
			$valuesArray = $this->getClientProfile($clientId);		
		
			# assign the values to the class' attributes
			# first check if the $valuesArray doesn't contain any query or system error
				if ($this->isError($valuesArray)) {
					# debugging purposes <remove on production>	
						//echo $this->errorArray["$valuesArray"];
				
					# return the error string
						return $this->errorArray[$valuesArray];
				}
				else {
					# assign the values from the database
						$this->profileArray = $valuesArray;
						
						//print_r($this->profileArray);
				}	
		# 2. get the client's address from the database
			$valuesArray = $this->getAddresses($clientId);		
		
			# assign the values to the class' attributes
			# first check if the $valuesArray doesn't contain any query or system error
				if ($this->isError($valuesArray)) {
					# debugging purposes <remove on production>	
						//echo $this->errorArray["$valuesArray"];
				
					# return the error string
						return $this->errorArray[$valuesArray];
				}
				else {
					# assign the values from the database
						$this->addressArray = $valuesArray;
						//print_r($this->addressArray);
				}	
		# 3. get the client's phone numbers from the database
			$valuesArray = $this->getPhoneNumbers($clientId);		
		
			# assign the values to the class' attributes
			# first check if the $valuesArray doesn't contain any query or system error
				if ($this->isError($valuesArray)) {
					# debugging purposes <remove on production>	
						//echo $this->errorArray["$valuesArray"];
				
					# return the error string
						return $this->errorArray[$valuesArray];
				}
				else {
					# assign the values from the database
						$this->addressArray = $valuesArray;
				}	
				
		# 4. get the client's mobile phone numbers from the database
			$valuesArray = $this->getMobileNumbers($clientId);		
		
			# assign the values to the class' attributes
			# first check if the $valuesArray doesn't contain any query or system error
				if ($this->isError($valuesArray)) {
					# debugging purposes <remove on production>	
						//echo $this->errorArray["$valuesArray"];
				
					# return the error string
						return $this->errorArray[$valuesArray];
				}
				else {
					# assign the values from the database
						$this->addressArray = $valuesArray;
				}	
	}	
	
	public function getClientProfile ($clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addslashes($clientId);
				# prepare the statement
					$statement = "SELECT * FROM clients WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getClientProfile]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							$resultArray = mysql_fetch_array($result);
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getProfileItem ($item, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addslashes($clientId);
					$item = addslashes($item);
				# prepare the statement
					$statement = "SELECT $item FROM clients WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getClientProfile]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							$resultArray = mysql_fetch_assoc($result);
						# return the values											
							return $resultArray[$item];
					}		
			//}
	}
	
	public function getPhoneNumbers ($clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addSlashes($clientId);
					$resultArray = array();
				# prepare the statement
					$statement = "SELECT * FROM client_phones WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getPhoneNumbers]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getMobileNumbers ($clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addSlashes($clientId);
					$resultArray = array();
				# prepare the statement
					$statement = "SELECT * FROM client_mobilephones WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getMobileNumber]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
	public function getAddresses ($clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/
				# protection against SQL injection, add slashes to escape all escape characters
					$clientId = addSlashes($clientId);
					$resultArray = array();
				# prepare the statement
					$statement = "SELECT * FROM client_addresses WHERE client_id = '$clientId'";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:getAddress]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "got it";
						
						# get the entry values
							while ($row = mysql_fetch_assoc($result)){ 
								$resultArray[] = $row; 
							}
						# return the values											
							return $resultArray;
					}		
			//}
	}
	
#####################
# GETTERS FUNCTIONS #	
#####################	
	
	# GETTERS #
	# the getters just gets the values of the class' attributes
	
		public function getProfileValues () {
			return $this->profileArray;
		}
	
		public function getAddressesValues () {
			return $this->addressArray;
		}
	
		public function getPhoneNumberValues () {
			return $this->phoneArray();
		}
	
		public function getMobileNumberValues () {
			return $this->mobileArray();
		}
	
		public function getProfileValue ($item) {
			return $this->profileArray[$item];
		}
	
	
#####################
# SETTERS FUNCTIONS #	
#####################	
	
	# SETTERS #
	# the setters stores the values to the class' attributes and then connects to the database to store the value
	# it is already assumed that the script that will provide the necessary input validation and error checking
	# the only security the setters provide is the addslashes() function to protect SQL injection from happening
	
		public function setProfileValue ($item, $value, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {	*/		
					$this->profileArray[$item] = addslashes($value); // the addslashes is for SQL injection protection
					$item = addslashes($item);
					# check if the item to be modified is the client_id. if it is, reject the modification
						if ($item == 'client_id') {
							# set the error ID to the class attribute
								$this->MESSAGE_HANDLE = 'DB_INVALIDFIELD';
					
							return $this->MESSAGE_HANDLE;
						}
						else {
							# prepare the statement
								$statement = "UPDATE clients SET $item  = '$value' WHERE client_id = '$clientId'";	
							# query & check if error has occured
								if(!$result = mysql_query($statement)) {
									return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:setProfileValue]';
								}
								else {
									# debugging purposes <remove on production>	
										//echo "updated the database";
				
									# return the values											
										return true;
								}
						}	
				//}
		}
		
		public function setAddressValue ($addressId, $item, $value, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {*/		
					$this->addressArray[$addressId][$item] = addslashes($value); // the addslashes is for SQL injection protection
					$item = addslashes($item);
					# prepare the statement
						$statement = "UPDATE client_addresses SET $item  = '$value' WHERE address_id = '$addressId' AND client_id = '$clientId'";
					# query & check if error has occured
					if(!$query = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:setAddressValue]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return true;
					}
				//}		
		}
		
		public function setPhoneNumberValue ($phoneId, $item, $value, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {		*/
					$this->phoneArray[$phoneId][$item] = addslashes($value); // the addslashes is for SQL injection protection
					$item = addslashes($item);
					# prepare the statement
						$statement = "UPDATE client_phones SET $item = '$value' WHERE phone_id = '$phoneId' AND client_id = '$clientId '";
					# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:setPhoneNumberValue]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return true;
					}
				//}	
		}
		
		public function setMobileNumberValue ($mobileId, $item, $value, $clientId) {
			# check if the DB_HANDLE has connected to the database
				/*if ($this->DB_HANDLE == NULL) {
					return $errorArray['DB_NOTCONNECTED'];
				}
				else {*/		
					$this->mobileArray[$mobileId][$item] = addslashes($value); // the addslashes is for SQL injection protection
					$item = addslashes($item);
					# prepare the statement
						$statement = "UPDATE client_mobilephones SET $item = '$value' WHERE mobile_id = '$mobileId' AND client_id = '$clientId'";
					# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:setMobileNumberValue]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return true;
					}
				//}	
		}

###############################	
# STORE SYSTEM DATA FUNCTIONS #	
###############################	
	
	# NOTE: #
	# it is already assumed that before passing the values to the ADD/STORE functions, proper input validation and error checking is already performed
	
	public function addNewClient ($valuesArray, $agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/		
				# prepare the statement 
					$statement = "INSERT INTO clients (				
							client_lastname, 
							client_firstname, 
							client_nickname, 
							client_middlename, 
							client_gender, 
							client_birthday, 
							client_batch, 
							client_coursecode, 
							client_coursename, 
							client_yeargraduated, 
							client_school, 
							client_company, 
							client_position, 
							client_email, 
							client_deceased, 
							client_dateofdeath, 
							client_type, 
							client_alumni, 
							agent_id
						) 
						VALUES (
							'{$valuesArray['client_lastname']}',
							'{$valuesArray['client_firstname']}',
							'{$valuesArray['client_nickname']}',
							'{$valuesArray['client_middlename']}',
							'{$valuesArray['client_gender']}',
							'{$valuesArray['client_birthday']}',
							'{$valuesArray['client_batch']}',
							'{$valuesArray['client_coursecode']}',
							'{$valuesArray['client_coursename']}',
							'{$valuesArray['client_yeargraduated']}',
							'{$valuesArray['client_school']}',
							'{$valuesArray['client_company']}',
							'{$valuesArray['client_position']}',
							'{$valuesArray['client_email']}',
							'{$valuesArray['client_deceased']}',
							'{$valuesArray['client_dateofdeath']}',
							'{$valuesArray['client_type']}',
							'{$valuesArray['client_alumni']}',
							'{$agentId}'
						)";
						
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:addNewClient]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return mysql_insert_id();
					}
			//}
	}
	
	public function addNewReferral ($profileValuesArray, $phoneValuesArray, $mobileValuesArray, $agentId, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/	
				# add the referred client as a new client (call the addNewClient() function)
					$referredId = $this->addNewClient($profileValuesArray, $agentId);
					
				# check if the $phoneValuesArray is set
					if ($phoneValuesArray['phone_number'] != '') {
						$this->addNewPhoneNumber($phoneValuesArray, $referredId);
					}
				# check if the $mobileValuesArray is set
					if ($mobileValuesArray['mobile_number'] != '') {
						$this->addNewMobileNumber($mobileValuesArray, $referredId);
					}
				
				# link the client to the referred client and agent (call the tagRefer() function)
					$result = $this->tagRefer($clientId, $referredId, $agentId);
					
					return $result;
			//}
	}
	
	public function addNewAddress ($valuesArray, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/	
				# prepare the statement
					$statement = "INSERT INTO client_addresses (
							address_street, 
							address_city, 
							address_municipality, 
							address_province, 
							address_country, 
							address_zipcode, 
							address_type, 
							client_id
						) 
						VALUES (
							'{$valuesArray['address_street']}',
							'{$valuesArray['address_city']}',
							'{$valuesArray['address_municipality']}',
							'{$valuesArray['address_province']}',
							'{$valuesArray['address_country']}',
							'{$valuesArray['address_zipcode']}',
							'{$valuesArray['address_type']}',
							'{$clientId}'
						)";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:addNewAddress]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return mysql_insert_id();
					}
			//}
	}
	
	public function addNewPhoneNumber ($valuesArray, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {	*/
				# prepare the statement
					$statement = "INSERT INTO client_phones (
								phone_countrycode, 
								phone_areacode, 
								phone_number, 
								phone_type, 
								phone_priority, 
								client_id
							) 
							VALUES (
								'{$valuesArray['phone_countrycode']}',
								'{$valuesArray['phone_areacode']}',
								'{$valuesArray['phone_number']}',
								'{$valuesArray['phone_type']}',
								'{$valuesArray['phone_priority']}',
								'{$clientId}'
							)";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:addNewPhoneNumber]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return mysql_insert_id();
					}
			//}
	}
	
	public function addNewMobileNumber ($valuesArray, $clientId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/	
				# prepare the statement
					$statement = "INSERT INTO client_mobilephones (
								mobile_countrycode, 
								mobile_provider, 
								mobile_number, 
								mobile_priority, 
								client_id) 
							VALUES (
								'{$valuesArray['mobile_countrycode']}',
								'{$valuesArray['mobile_provider']}',
								'{$valuesArray['mobile_number']}',
								'{$valuesArray['mobile_priority']}',
								'{$clientId}'
							)";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:addNewMobileNumber]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return mysql_insert_id();
					}
			//}
	}

	public function tagRefer ($clientId, $referredId, $agentId) {
		# check if the DB_HANDLE has connected to the database
			/*if ($this->DB_HANDLE == NULL) {
				return $errorArray['DB_NOTCONNECTED'];
			}
			else {*/	
				$agentId = addslashes($agentId); // for SQL injection protection (just in case)
				$refferedId = addslashes($refferedId);
				$clientId = addslashes($clientId);
				# prepare the statement
					$statement = "INSERT INTO referrals (
							referrer_id, 
							referred_id, 
							agent_id
						) 
						VALUES (
							'{$clientId}',
							'{$referredId}',
							'{$agentId}'
						)";
				# query & check if error has occured
					if(!$result = mysql_query($statement)) {
						return $errorArray['DB_QUERYERROR'] . ' -[ClientHandle:tagRefer]';
					}
					else {
						# debugging purposes <remove on production>	
							//echo "updated the database";
				
						# return the values											
							return true;
					}
			//}
	}

########################	
# MANAGEMENT FUNCTIONS #	
########################
	private function isError ($value) {
		switch($value) {
			 case 'DB_NOTCONNECTED':
			 case 'DB_QUERYERROR':
			 case 'DB_INVALIDFIELD':			 	
			 	return true;
			 break;					 	 
	 	}	
	}
		
}	
?>
